﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace YunQue.Core.Common.Helpers
{
    /// <summary>
    /// sql 解析
    /// </summary>
    public class SqlAnalysis
    {
        //换行符，制表符、多空格
        private static readonly Regex _enterRegex = new Regex(@"\r|\n|\t|\s{2,}");

        //先截取 union all  union on 的查询,比如 select a from a union all select a from b,替换为 T_000001 union all T_0000002
        private static readonly Regex _unionRegex = new Regex(@"(\s+union\s+all|\s+union\s+on)");
        //括号内的内容
        private static readonly Regex _bracketContentRegex = new Regex(@"\((?<bracketContent>[\s\S]+?)\)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //查询的字段
        private static readonly Regex _selectTextRegex = new Regex(@"select\s+(?<selectText>[\s\S]+?)\s+from\s+", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //连接的表以及连接类型 取join 和on 之间的内容
        private static readonly Regex _joinTableRegex = new Regex(@"\s(?<joinType>left\s+join|right\s+join|inner\s+join|full\s+join|cross\s+join|join)\s+(?<joinTableName>[\s\S]+?)\s+on\s+", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //查询的表
        private static readonly Regex _fromTableRegex = new Regex(@"\sfrom\s+(?<tableName>[\s\S]+?)\s*(where|left\s+join|right\s+join|inner\s+join|full\s+join|cross\s+join|join|group\s+by|order\s+by|limit)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //where 条件
        private static readonly Regex _whereConditionRegex = new Regex(@"\swhere\s+(?<whereCondition>[\s\S]+?)\s*(order\s+by|group\s+by|limit|$)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //group by 字段
        private static readonly Regex _groupByFieldRegex = new Regex(@"\sgroup\s+by\s+(?<groupByField>[\s\S]+?)\s*(order\s+by|limit|$)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //order by 字段
        private static readonly Regex _orderByFieldRegex = new Regex(@"\sorder\s+by\s+(?<orderByField>[\s\S]+?)\s*(limit|$)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //查询语句
        private static readonly Regex _querySqlRegex = new Regex(@"^\s*select\s+[\s\S]+?\s+from\s+[\s\S]+", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //四则运算
        private static readonly Regex _arithmeticRegex = new Regex(@"\s*(?<leftOperand>[\w\.]+)\s*(?<operator>\+|\-|\*|\/)\s*(?<rightOperand>[\w\.]+)\s*", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //case when end 等
        private static readonly Regex _caseWhenRegex = new Regex(@"\b(?<functionName>CASE|WHEN|THEN|ELSE|END|IF|ELSEIF)\b", RegexOptions.IgnoreCase | RegexOptions.Compiled);
        //sql 函数
        private static readonly List<string> _functionNames = new List<string>
        {
            "AVG","COUNT","SUM","MIN","MAX","LEN","SUBSTRING","ROUND","NOW","GETDATE","ISNULL","COALESCE","CONVERT","CAST","LTRIM","RTRIM","UPPER","LOWER","ABS","UNIQ"
        };

        /// <summary>
        /// 替换sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static Tuple<Dictionary<string, string>, string> ReplaceSql(string sql)
        {
            var replacements = new Dictionary<string, string>();
            sql = FormatText(sql);
            //1. 先截取 union all  union on 的查询,比如 select a from a union all select a from b,替换为 T_000001 union all T_0000002
            var unionArray = _unionRegex.Split(sql);
            if (unionArray.Length > 1)
            {
                for (int i = 0; i < unionArray.Length; i++)
                {
                    if (i % 2 == 0)
                    {
                        var sourceContent = unionArray[i];
                        var placeholder = GeneratePlaceholder(replacements.Count + 1);
                        replacements[placeholder] = sourceContent;
                        sql = sql.Replace(sourceContent, placeholder);
                    }
                }
            }
            //2.优先替换括号的内容，防止子查询影响整体解析
            ReplaceNestedParentheses(ref sql, replacements);
            //3.替换select 字段
            var selectMatch = _selectTextRegex.Match(sql);
            if (selectMatch.Success)
            {
                var selectText = selectMatch.Groups["selectText"].Value;
                var placeholder = GeneratePlaceholder(replacements.Count + 1);
                replacements[placeholder] = selectText;
                sql = sql.Replace(selectText, placeholder);
            }
            //4.替换from 表名
            var fromMatch = _fromTableRegex.Match(sql);
            if (fromMatch.Success)
            {
                var tableName = fromMatch.Groups["tableName"].Value;
                var placeholder = GeneratePlaceholder(replacements.Count + 1);
                replacements[placeholder] = tableName;
                sql = sql.Replace(tableName, $"{placeholder}");
            }
            //5.替换join 表名
            var joinMatches = _joinTableRegex.Matches(sql);
            foreach (Match match in joinMatches)
            {
                var tableName = match.Groups["joinTableName"].Value;
                var joinType = match.Groups["joinType"].Value;
                var placeholder = GeneratePlaceholder(replacements.Count + 1);
                replacements[placeholder] = tableName;
                sql = sql.Replace(tableName, placeholder);
            }

            //6.替换where 条件
            var whereMatch = _whereConditionRegex.Match(sql);
            if (whereMatch.Success)
            {
                var whereCondition = whereMatch.Groups["whereCondition"].Value;
                var placeholder = GeneratePlaceholder(replacements.Count + 1);
                replacements[placeholder] = whereCondition;
                sql = sql.Replace(whereCondition, placeholder);
            }
            //7.替换group by 字段
            var groupByMatch = _groupByFieldRegex.Match(sql);
            if (groupByMatch.Success)
            {
                var groupByField = groupByMatch.Groups["groupByField"].Value;
                var placeholder = GeneratePlaceholder(replacements.Count + 1);
                replacements[placeholder] = groupByField;
                sql = sql.Replace(groupByField, placeholder);
            }
            //8.替换order by 字段
            var orderByMatch = _orderByFieldRegex.Match(sql);
            if (orderByMatch.Success)
            {
                var orderByField = orderByMatch.Groups["orderByField"].Value;
                var placeholder = GeneratePlaceholder(replacements.Count + 1);
                replacements[placeholder] = orderByField;
                sql = sql.Replace(orderByField, placeholder);
            }
            return new Tuple<Dictionary<string, string>, string>(replacements, sql);
        }
        /// <summary>
        /// 生成占位符
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        private static string GeneratePlaceholder(int index)
        {
            return $"T_{index:D7}";
        }
        /// <summary>
        /// 格式化文本
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string FormatText(string sql)
        {
            //将换行，多个空格格式化
            return _enterRegex.Replace(sql, " ");
        }
        /// <summary>
        /// 是查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool IsQuerySql(string sql)
        {
            sql = FormatText(sql);
            return _querySqlRegex.IsMatch(sql);
        }
        /// <summary>
        /// 是否是函数
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public static Tuple<bool, string, string> IsFunction(string text)
        {
            text = FormatText(text);
            var isMatch = _caseWhenRegex.IsMatch(text);
            if (isMatch)
            {
                return new Tuple<bool, string, string>(true, string.Empty, string.Empty);
            }
            foreach (var functionName in _functionNames)
            {
                var functionRegex = new Regex($@"\b{functionName}\s*\((?<functionParam>[\s\S]*?)\)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
                var match = functionRegex.Match(text);
                if (match.Success)
                {
                    return new Tuple<bool, string, string>(true, functionName, match.Groups["functionParam"].Value);
                }
            }
            return new Tuple<bool, string, string>(false, string.Empty, string.Empty);
        }
        /// <summary>
        /// 是否是四则运算
        /// 支持多个四则运算符，比如 a + b - c * d / e
        /// 解析为 leftOperand operator rightOperand
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public static Arithmetic ParseArithmeticExpression(string text)
        {
            text = FormatText(text);
            // 如果整个表达式被括号包裹，去掉外层括号
            if (IsWrappedInParentheses(text))
            {
                text = text.Substring(1, text.Length - 2).Trim();
            }
            // 先找顶层 + 或 -
            int pos = FindTopLevelOperator(text, new[] { '+', '-' });
            if (pos != -1)
            {
                return new Arithmetic
                {
                    Operator = text[pos].ToString(),
                    LeftOperand = ParseArithmeticExpression(text.Substring(0, pos)),
                    RightOperand = ParseArithmeticExpression(text.Substring(pos + 1))
                };
            }

            // 再找顶层 * 或 /
            pos = FindTopLevelOperator(text, new[] { '*', '/' });
            if (pos != -1)
            {
                return new Arithmetic
                {
                    Operator = text[pos].ToString(),
                    LeftOperand = ParseArithmeticExpression(text.Substring(0, pos)),
                    RightOperand = ParseArithmeticExpression(text.Substring(pos + 1))
                };
            }
            // 没有运算符，就是叶子节点
            return new Arithmetic { Value = text };
        }
        /// <summary>
        /// 还原四则运算表达式
        /// </summary>
        /// <param name="arithmetic"></param>
        /// <returns></returns>
        public static string RestoreArithmetic(Arithmetic arithmetic)
        {
            if (arithmetic == null) return string.Empty;
            if (arithmetic.Value != null)
            {
                return arithmetic.Value;
            }
            string left = RestoreArithmetic(arithmetic.LeftOperand);
            string right = RestoreArithmetic(arithmetic.RightOperand);
            return $"({left} {arithmetic.Operator} {right})";
        }


        public static bool IsWrappedInParentheses(string expr)
        {
            if (!expr.StartsWith("(") || !expr.EndsWith(")")) return false;
            int depth = 0;
            for (int i = 0; i < expr.Length; i++)
            {
                if (expr[i] == '(') depth++;
                else if (expr[i] == ')') depth--;
                if (depth == 0 && i < expr.Length - 1) return false; // 提前结束
            }
            return true;
        }

        public static int FindTopLevelOperator(string expr, char[] ops)
        {
            int depth = 0;
            for (int i = 0; i < expr.Length; i++)
            {
                char c = expr[i];
                if (c == '(') depth++;
                else if (c == ')') depth--;
                else if (depth == 0 && Array.IndexOf(ops, c) != -1)
                {
                    return i;
                }
            }
            return -1;
        }

        /// <summary>
        /// 处理嵌套括号，替换为占位符
        /// </summary>
        /// <param name="text"></param>
        /// <param name="replacements"></param>
        public static void ReplaceNestedParentheses(ref string text, Dictionary<string, string> replacements)
        {
            if (replacements == null) replacements = new Dictionary<string, string>();
            Regex regex = new Regex(@"\([^()]*\)"); // 匹配最内层括号
            while (true)
            {
                Match match = regex.Match(text);
                if (!match.Success)
                    break;

                string replacementKey = GeneratePlaceholder(replacements.Count + 1);
                string originalValue = match.Value; // 包含括号
                replacements[replacementKey] = originalValue;
                text = text.Substring(0, match.Index) + replacementKey + text.Substring(match.Index + match.Length);
            }
        }
        /// <summary>
        /// 还原嵌套括号
        /// </summary>
        /// <param name="text"></param>
        /// <param name="replacements"></param>
        /// <returns></returns>
        public static string RestoreNestedParentheses(string text, Dictionary<string, string> replacements)
        {
            if (replacements == null || replacements.Count == 0) return text;
            foreach (var kvp in replacements.OrderByDescending(k => k.Key))
            {
                text = text.Replace(kvp.Key, kvp.Value);
            }
            return text;
        }
    }


    public class Arithmetic
    {
        public string Operator { get; set; }
        public string Value { get; set; }
        public Arithmetic LeftOperand { get; set; }
        public Arithmetic RightOperand { get; set; }
    }
}
